import { ResultSetHeader, RowDataPacket } from "mysql2";
import pool from '../config/db'
import redisClient from '../utils/redisClient';

interface Post {
    id: number;
    user_id: number;
    title: string;
    content: string;
    image_url: string | null;
    created_at: Date;
    updated_at: Date;
    username: string; // 用户的名字
}

// 根据用户的id获取对应的作品 
export const getPostByUser = async (user_id: number, page: number = 1, limit: number = 10): Promise<Post[]> => {
    try {
        const offset = (page - 1) * limit;
        const cacheKey = `user:${user_id}:posts:page:${page}:limit:${limit}`;
        const cachedPosts = await redisClient.get(cacheKey);
        if (cachedPosts) {
            return JSON.parse(cachedPosts) as Post[];
        }
        const [rows] = await pool.query<RowDataPacket[]>(
            `SELECT posts.id, posts.user_id, posts.title, posts.content, posts.image_url, posts.created_at, posts.updated_at, users.username
             FROM posts
             JOIN users ON posts.user_id = users.id
             WHERE posts.user_id = ?
             ORDER BY posts.created_at DESC
             LIMIT ? OFFSET ?`,
            [user_id, limit, offset]
        );
        const posts = rows as Post[];
        // 将查询结果缓存到 Redis，设置过期时间（例如 1 小时）
        await redisClient.set(cacheKey, JSON.stringify(posts), { EX: 36000 });

        return posts;
    } catch (error) {
        console.error('查询作品时出错:', error);
        throw error;
    }

}

// 创建作品 
export const creatPost = async (user_id: number, title: string, content: string, image_urls: string[]): Promise<Post> => {
    // 从连接池中获取一个连接
    const connection = await pool.getConnection();
    try {
        // 开始事务
        await connection.beginTransaction();
        console.log('开始事务了')
        // 插入新的作品
        const [result] = await connection.query<ResultSetHeader>(
            'INSERT INTO posts (user_id, title, content, image_url, created_at, updated_at) VALUES (?, ?, ?, ?, NOW(), NOW())',
            [user_id, title, content, JSON.stringify(image_urls)]
        );
        console.log('result', result)
        if (result.affectedRows === 0) {
            throw new Error('数据库插入失败');
        }
        // 获取插入的帖子的 ID
        const insertId = result.insertId;
        // 提交事务 
        await connection.commit();
        // 查询刚刚创建的作品，包含作者的用户名
        const [rows] = await connection.query<RowDataPacket[]>(
            `SELECT posts.id, posts.user_id, posts.title, posts.content, posts.image_url, posts.created_at, posts.updated_at, users.name
     FROM posts
     JOIN users ON posts.user_id = users.id
     WHERE posts.id = ?`,
            [insertId]
        );
        if (rows.length === 0) {
            throw new Error('作品创建后未找到');
        }
        const post = rows[0] as Post;
        post.image_url = post.image_url ? JSON.parse(post.image_url) : [];
        const cacheKey = `post:${insertId}`;
        await redisClient.set(cacheKey, JSON.stringify(post), { EX: 3600 }); // 缓存 1 小时
        return post;
    } catch (error) {
        await connection.rollback();
        throw error;
    } finally {
        connection.release();
    }
};

